package cn.cdutetc.library.common;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 数据库备份工具类
 */
@Slf4j
@Component
public class BackupUtils {

    @Value("${backup.path:/backups}")
    private String backupPath;

    private final DataSource dataSource;

    public BackupUtils(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 执行数据库备份
     * @param backupName 备份名称
     * @param description 备份描述
     * @return 备份文件路径
     */
    public String backupDatabase(String backupName, String description) {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String fileName = String.format("%s_%s.sql", backupName, timestamp);
        String filePath = Paths.get(backupPath, fileName).toString();

        try {
            // 确保备份目录存在
            Path backupDir = Paths.get(backupPath);
            if (!Files.exists(backupDir)) {
                Files.createDirectories(backupDir);
            }

            // 执行备份
            performBackup(filePath);
            
            log.info("数据库备份成功: {}", filePath);
            return filePath;
        } catch (Exception e) {
            log.error("数据库备份失败", e);
            throw new RuntimeException("数据库备份失败: " + e.getMessage());
        }
    }

    /**
     * 执行备份操作
     */
    private void performBackup(String filePath) throws Exception {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             PrintWriter writer = new PrintWriter(new FileWriter(filePath))) {

            // 写入备份文件头部信息
            writer.println("-- 数据库备份文件");
            writer.println("-- 备份时间: " + LocalDateTime.now());
            writer.println("-- 数据库: " + connection.getCatalog());
            writer.println();

            // 获取所有表名
            List<String> tables = getAllTables(statement);
            
            // 备份每个表的数据
            for (String table : tables) {
                backupTable(statement, writer, table);
            }

            writer.flush();
        }
    }

    /**
     * 获取所有表名（排除备份相关表）
     */
    private List<String> getAllTables(Statement statement) throws Exception {
        List<String> tables = new ArrayList<>();
        List<String> excludeTables = Arrays.asList("backup_record", "import_export_record", "system_log");
        
        try (ResultSet rs = statement.executeQuery("SHOW TABLES")) {
            while (rs.next()) {
                String tableName = rs.getString(1);
                // 排除备份相关表，避免循环备份
                if (!excludeTables.contains(tableName)) {
                    tables.add(tableName);
                }
            }
        }
        return tables;
    }

    /**
     * 备份单个表
     */
    private void backupTable(Statement statement, PrintWriter writer, String tableName) throws Exception {
        writer.println("-- 表: " + tableName);
        writer.println("DROP TABLE IF EXISTS `" + tableName + "`;");
        writer.println();

        // 获取表结构
        try (ResultSet rs = statement.executeQuery("SHOW CREATE TABLE `" + tableName + "`")) {
            if (rs.next()) {
                writer.println(rs.getString(2) + ";");
                writer.println();
            }
        }

        // 获取表数据
        try (ResultSet rs = statement.executeQuery("SELECT * FROM `" + tableName + "`")) {
            int columnCount = rs.getMetaData().getColumnCount();
            
            while (rs.next()) {
                StringBuilder insertSql = new StringBuilder("INSERT INTO `" + tableName + "` VALUES (");
                
                for (int i = 1; i <= columnCount; i++) {
                    if (i > 1) insertSql.append(", ");
                    
                    Object value = rs.getObject(i);
                    if (value == null) {
                        insertSql.append("NULL");
                    } else if (value instanceof String) {
                        insertSql.append("'").append(value.toString().replace("'", "''")).append("'");
                    } else if (value instanceof java.sql.Timestamp) {
                        // Format timestamp as MySQL datetime (YYYY-MM-DD HH:MM:SS)
                        java.sql.Timestamp timestamp = (java.sql.Timestamp) value;
                        String formattedTimestamp = timestamp.toString().replace('T', ' ').substring(0, 19);
                        insertSql.append("'").append(formattedTimestamp).append("'");
                    } else if (value instanceof java.sql.Date) {
                        // Format date as MySQL date
                        java.sql.Date date = (java.sql.Date) value;
                        insertSql.append("'").append(date.toString()).append("'");
                    } else if (value instanceof java.sql.Time) {
                        // Format time as MySQL time
                        java.sql.Time time = (java.sql.Time) value;
                        insertSql.append("'").append(time.toString()).append("'");
                    } else {
                        insertSql.append(value.toString());
                    }
                }
                
                insertSql.append(");");
                writer.println(insertSql.toString());
            }
        }
        
        writer.println();
    }


    /**
     * 获取备份文件大小
     */
    public long getFileSize(String filePath) {
        try {
            return Files.size(Paths.get(filePath));
        } catch (Exception e) {
            log.error("获取文件大小失败", e);
            return 0;
        }
    }

    /**
     * 删除备份文件
     */
    public boolean deleteBackupFile(String filePath) {
        try {
            Files.deleteIfExists(Paths.get(filePath));
            log.info("备份文件删除成功: {}", filePath);
            return true;
        } catch (Exception e) {
            log.error("删除备份文件失败", e);
            return false;
        }
    }

    /**
     * 获取备份目录下的所有备份文件
     */
    public List<String> getBackupFiles() {
        List<String> files = new ArrayList<>();
        try {
            Path backupDir = Paths.get(backupPath);
            if (Files.exists(backupDir)) {
                Files.walk(backupDir)
                    .filter(Files::isRegularFile)
                    .filter(path -> path.toString().endsWith(".sql"))
                    .forEach(path -> files.add(path.toString()));
            }
        } catch (Exception e) {
            log.error("获取备份文件列表失败", e);
        }
        return files;
    }
}
